package com.example.demo.dao;

import com.example.demo.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;

/**
 * 参考文档 https://blog.csdn.net/choushi300/article/details/71438693
 *
 * @author vsalw
 */
@Repository
public interface UserRepository extends JpaRepository<User, Integer> {

    /**
     * jpa根据方法名自动解析,不需要SQL语句实现
     */
    User findByBirthdayContains(String name);

    /**
     * 通过方法名查询用户
     *
     * @param username
     * @return
     */
    User findByUsername(String username);

    /**
     * Hql语法 查询写法
     * where username=?1
     *
     * @param sex
     * @return
     */
    @Query(value = "from User u where u.sex=:sex")
    User findUserBySex(@Param("sex") String sex);

    /**
     * 原生sql 查询写法
     *
     * @param username
     * @return
     */
    @Query(value = " SELECT * from t_user where username=:username", nativeQuery = true)
    User findUserByUsername(@Param("username") String username);

    /**
     * 原生sql 修改，使用事务
     *
     * @param sex
     * @param username
     */
    @Modifying
    @Transactional
    @Query(value = " UPDATE t_user SET username=:username WHERE sex=:sex", nativeQuery = true)
    void updateBySexSql(@Param("sex") String sex, @Param("username") String username);

    /**
     * Hql语法 修改，使用事务
     *
     * @param sex
     * @param username
     */
    @Modifying
    @Transactional
    @Query(value = " UPDATE User SET username=:username WHERE sex=:sex")
    void updateBySexHql(@Param("sex") String sex, @Param("username") String username);
}
